package cn.tedu.utils.jdbc;/**
 * @Author 金帮嘉诚 @Date 2021-07-15 星期四 11:11
 */
/*本类用于测试*/

import cn.tedu.pojo.Student;
import cn.tedu.utils.others.dataTypeUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 知识点
 * spb_student表的crud操作
 * id int PRIMARY KEY auto_increment ,
 * name varchar(100),
 * age tinyint,
 * gender int not null DEFAULT 0,
 * favorite int,
 * eduBackground int,
 * admissionDate date,
 */
public class StuTb {

    /**
     * 按id查找
     */
    public static List<Student> select(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {

            conn = JdbcUtils.getCon();
//            String sql = "select * from spb_student where " +
//                    "id = ? and name = ? and" +
//                    "age = ? and gender = ? and " +
//                    "favorite = ? and eduBackground = ? and " +
//                    "admissionDate = ?";
            String sql = "select * from spb_student where id = ?";
            ps = conn.prepareStatement(sql);

            ps.setString(1, String.valueOf(student.getId()));

            rs = ps.executeQuery();

            return getStuListByRs(rs);

        } catch (Exception throwables) {
            throwables.printStackTrace();
            System.out.println("Error 不能执行");
            return null;
        } finally {
            JdbcUtils.closeRPC(rs, ps, conn);
        }
    }

    /**
     * 根据结果集rs得到List<student>对象 studentList
     */
    public static List<Student> getStuListByRs(ResultSet resultSet) throws SQLException {
        List<Student> studentList = new ArrayList<>();
        int i = 0;
        while (resultSet.next()) {
            Student student1 = new Student();

            student1.setId(Integer.valueOf(resultSet.getString(1)));

            student1.setName(resultSet.getString(2));
            student1.setAge(resultSet.getInt(3));
            student1.setSex(resultSet.getInt(4));
            student1.setLike(resultSet.getInt(5));
            student1.setEduBackground(resultSet.getInt(6));
            student1.setAdmissionDate(resultSet.getDate(7));

            studentList.add(i, student1);
        }
        return studentList;
    }

    /**
     * 插入
     */
    public static String insert(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            /*
            * id int PRIMARY KEY auto_increment ,
            * name varchar(100),
              age tinyint,
              gender int not null DEFAULT 0,
              favorite int,
              eduBackground int,
              admissionDate date,*/
            conn = JdbcUtils.getCon();
            String sql = "insert into spb_student values(null,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setInt(2, student.getAge());
            ps.setInt(3, student.getSex());
            ps.setInt(4, student.getLike());
            ps.setInt(5, student.getEduBackground());

            java.sql.Date admissionDateSql = dataTypeUtils.toSqlDate(student.getAdmissionDate());
            ps.setDate(6, admissionDateSql);

            if (ps.executeUpdate() == 1) {
                return "insert success";
            } else {
                return "insert error1";
            }


        } catch (Exception throwables) {
            throwables.printStackTrace();
            return "insert error 2";
        } finally {
            JdbcUtils.closeRPC(rs, ps, conn);
        }
    }

    /**
     * 删除 按id和name
     */
    public static String delete(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            /*
            * id int PRIMARY KEY auto_increment ,
            * name varchar(100),
              age tinyint,
              gender int not null DEFAULT 0,
              favorite int,
              eduBackground int,
              admissionDate date,*/
            conn = JdbcUtils.getCon();
            String sql = "delete from spb_student where id = ? and name = ?;";
//            String sql = "delete from spb_student where name = ?;";
            ps = conn.prepareStatement(sql);
            ps.setString(1, String.valueOf(student.getId()));//valueOf()不会报空指针异常，比toString()好
            ps.setString(2, student.getName());

            //insert update delete
            if (ps.executeUpdate() == 1) {
                return "delete success";
            } else {
                return "delete error1 没有该数据";
            }

        } catch (Exception throwables) {
            throwables.printStackTrace();
            return "delete error 2 语句执行错误";
        } finally {
            JdbcUtils.closeRPC(rs, ps, conn);
        }
    }

}
